﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAL.App_Code;

namespace DAL.DataOperate
{
    public class OrderDAL:DAL.App_Code.DBBase
    {
        public OrderDAL() :
            base() { }

        #region 查找订单(管理员查看)
        public DataSet SelectAll()
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from tbOrder ");
            sqlStr.Append("where Ord_Idint not in (select Ord_Idint from tbSecondOrder_Seller)");

            SqlParameter[] pa = { };

            return ExecuteString(sqlStr.ToString(), pa, "tbOrder");
        }
        public DataSet SelectByID(int OrderId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from vi_Order ");
            sqlStr.Append("where Ord_Idint= ");
            sqlStr.Append("@Ord_Idint");

            SqlParameter[] pa = {
                  new SqlParameter("@Ord_Idint", OrderId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbOrder");
        }
        public DataSet SelectByUserName(String UserName)
        {
            StringBuilder sqlStr = new StringBuilder();
            DAL.DataOperate.UserDAL userDAL = new UserDAL();
            DataSet ds = new DataSet();
            int userId = 0;
            ds = userDAL.SelectByName(UserName);
            if ((ds == null) ||
              (ds.Tables.Count == 0) ||
              (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            { return null; }
            else
            {
                userId = Int32.Parse( ds.Tables[0].Rows[0]["User_Idint"].ToString());
                sqlStr.Append("select * from tbOrder ");
                sqlStr.Append("where User_Idint = @User_Idint");
                SqlParameter[] pa = {
                     new SqlParameter("@User_Idint",userId)
                                     };

                return ExecuteString(sqlStr.ToString(), pa, "tbOrder");
            }
        }
        public DataSet SelectDetail(int DetailId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select Book_Idint,User_Idint from tbOrder_Detail ");
            sqlStr.Append("join tbOrder on tbOrder_Detail.Ord_Idint = tbOrder.Ord_Idint ");
            sqlStr.Append("and Detail_Idint = @Detail_Idint ");

            SqlParameter[] pa = {
                new SqlParameter("@Detail_Idint",DetailId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbOrder_Detail");
        }
        #endregion

        #region 操作订单 增删改
        public bool InsertOrder(Model.Order order, ref int InsertedorderId)
        {
            int affectedRow = 0;
            SqlParameter[] pa = {
                new SqlParameter("@User_Idint",order.User_Idint),
                new SqlParameter("@Ord_ContactManstr",order.Ord_ContactManstr),
                new SqlParameter("@Ord_Emailstr",order.Ord_Emailstr),
                new SqlParameter("@Ord_Telephonestr",order.Ord_Telephonestr),
                new SqlParameter("@Ord_PostalCodestr",order.Ord_PostalCodestr),
                new SqlParameter("@Ord_Addressstr",order.Ord_Addressstr),
                new SqlParameter("@Ord_Statusint",order.Ord_Statusint),
                new SqlParameter("@Ord_IsCheckedint",order.Ord_IsCheckedint),
                new SqlParameter("@Ord_Deliverystr",order.Ord_Deliverystr),
                new SqlParameter("@Ord_Paymentstr",order.Ord_Paymentstr),
                new SqlParameter("@Ord_Remarkstr",order.Ord_Remarkstr)
                                };

            InsertedorderId = ExecuteOutProcedure("sp_InsertOrder", pa, out affectedRow);  // 输出值为刚插入的订单号
            return affectedRow > 0 ? true : false;
        }
        public bool UpdataOrder(Model.Order order)
        {
            return true;
        }
        public bool UpdateOrderStatus(int OrderId, int newStatus)       // 订单状态（用户）
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            sqlStr.Append("update vi_Order set Ord_Statusint = ");
            sqlStr.Append("@Ord_Statusint ");
            sqlStr.Append("where Ord_Idint = ");
            sqlStr.Append("@Ord_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@Ord_Statusint",newStatus),
                new SqlParameter("@Ord_Idint",OrderId)
                                };

            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;
        }
        public bool UpdateOrderChecked(int OrderId, int newChecked)     // 订单状态（管理员）
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            sqlStr.Append("update vi_Order set Ord_IsCheckedint = ");
            sqlStr.Append("@Ord_IsCheckedint ");
            sqlStr.Append("where Ord_Idint = ");
            sqlStr.Append("@Ord_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@Ord_IsCheckedint",newChecked),
                new SqlParameter("@Ord_Idint",OrderId)
                                };

            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;
        }
        public bool DeleteOrder(int OrderId)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;

            sqlStr.Append("delete from vi_Order ");
            sqlStr.Append("where Ord_Idint = ");
            sqlStr.Append("@Ord_Idint");

            SqlParameter[] pa = {
                  new SqlParameter("@Ord_Idint", OrderId)
                                };

            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;
        }
        #endregion

        #region 操作订单详情 增删改
        public bool InsertOrderDetail(Model.Order order)
        {
            int affectedRow = 0;
            int returnValue = 0;

            SqlParameter[] pa = {
               new SqlParameter("@Ord_Idint",order.Ord_Idint),
               new SqlParameter("@Book_Idint",order.Book_Idint),
               new SqlParameter("@Detail_Quantityint",order.Detail_Quantityint),
               new SqlParameter("@Ord_Datedate",order.Ord_Datedate),
               new SqlParameter("@Detail_TotalCostflo",order.Detail_TotalCostflo),
               new SqlParameter("@Detail_IsCommentbit",0)// 新增的订单没有评论过
                                };

            returnValue = ExecuteRetProcedure("sp_InsertOrderDetail", pa, out affectedRow);
            return returnValue > 0 ? true : false;
        }
        public bool UpdatreOrderDetail(Model.Order order)
        {
            return true;
        }
        public bool UpdatreOrderDetail(int Detail_Idint, int Detail_IsCommentbit)
        {
            StringBuilder sqlStr = new StringBuilder();
            int affectedRow = 0;
            sqlStr.Append("update vi_Order ");
            sqlStr.Append("set Detail_IsCommentbit = @Detail_IsCommentbit ");
            sqlStr.Append("where Detail_Idint = @Detail_Idint ");

            SqlParameter[] pa = {
                new SqlParameter("@Detail_IsCommentbit",Detail_IsCommentbit),
                new SqlParameter("@Detail_Idint",Detail_Idint)
                                };

            ExecuteString(sqlStr.ToString(), pa, out affectedRow);
            return affectedRow > 0 ? true : false;
        }
        public bool DeleteOrderDetail()
        {
            return true;
        }
        #endregion

        #region 订单添加事物
        // 添加订单，如果某一个商品添加失败，整个事务回滚
        public bool InsertOrderTransaction(Model.Order order, DataTable productTable)
        {
            try
            {
                int insertdOrderId = 0;  // 插入的订单号
                int affectedRow = 0;     // 影响行数
                int UserId = 0;          // 如果是个人卖的

                SqlParameter[] pa = {
                new SqlParameter("@User_Idint",order.User_Idint),
                new SqlParameter("@Ord_ContactManstr",order.Ord_ContactManstr),
                new SqlParameter("@Ord_Emailstr",order.Ord_Emailstr),
                new SqlParameter("@Ord_Telephonestr",order.Ord_Telephonestr),
                new SqlParameter("@Ord_PostalCodestr",order.Ord_PostalCodestr),
                new SqlParameter("@Ord_Addressstr",order.Ord_Addressstr),
                new SqlParameter("@Ord_Statusint",order.Ord_Statusint),
                new SqlParameter("@Ord_IsCheckedint",order.Ord_IsCheckedint),
                new SqlParameter("@Ord_Deliverystr",order.Ord_Deliverystr),
                new SqlParameter("@Ord_Paymentstr",order.Ord_Paymentstr),
                new SqlParameter("@Ord_Remarkstr",order.Ord_Remarkstr),
                new SqlParameter("@Ord_Datedate",order.Ord_Datedate)
                                };

                SqlCommand cmd = new SqlCommand();
                cmd = BuildOutCommand("sp_InsertOrder", pa, CommandType.StoredProcedure);

                affectedRow = cmd.ExecuteNonQuery();                    // 影响的行数
                insertdOrderId = (int)cmd.Parameters["@output"].Value;// 返回参数(订单号）

                if (affectedRow > 0 && insertdOrderId.ToString() != "")
                {
                    SqlTransaction tran = Connection.BeginTransaction(IsolationLevel.ReadUncommitted);// 创建事物

                    try
                    {
                        foreach (DataRow dr in productTable.Rows)           // 遍历物品列表 
                        {
                            UserId = Int32.Parse(dr["OwnerId"].ToString()); // 获得编号
                            float totalPrice = Int32.Parse(dr["Book_Count"].ToString()) * float.Parse(dr["Book_Price"].ToString());

                            SqlParameter[] pa1 = {
                               new SqlParameter("@Ord_Idint",insertdOrderId),
                               new SqlParameter("@Book_Idint", Int32.Parse(dr["Book_Idint"].ToString())),
                               new SqlParameter("@Detail_Quantityint", Int32.Parse(dr["Book_Count"].ToString())),
                               new SqlParameter("@Detail_TotalCostflo",totalPrice),
                               new SqlParameter("@Detail_IsCommentbit","0") // 新增的订单没有评论过
                                            };

                            SqlCommand cmd1 = BuildOutCommand("sp_InsertOrderDetail", pa1, CommandType.StoredProcedure);
                            cmd1.Transaction = tran;             // 事物开始
                            affectedRow = cmd1.ExecuteNonQuery();// 影响的行数

                            if (affectedRow < 0)
                            {
                                DeleteOrder(insertdOrderId);    // 有一个失败，就把订单删除
                                return false;                   // 返回false
                            }
                            else                                // 订单添加成功，修改图书数量
                            {
                                SqlParameter[] pa2 = {
                                new SqlParameter("@Book_Idint",Int32.Parse(dr["Book_Idint"].ToString())),  // 图书编号
                                new SqlParameter("@BuyCount",Int32.Parse(dr["Book_Count"].ToString()))     // 购买数量
                                            };

                                SqlCommand cmd2 = BuildRetCommand("sp_UpdateBookCount", pa2, CommandType.StoredProcedure);
                                cmd2.Transaction = tran;
                                cmd2.ExecuteNonQuery();        // 修改图书数量
                            }
                        }
                        if (UserId > 0)                    // 如果是个人买二手书，添加
                        {
                            SqlParameter[] pa3 ={
                                  new SqlParameter("@Ord_Idint",insertdOrderId),
                                  new SqlParameter("@Seller_Idint",UserId)
                                            };

                            SqlCommand cmd3 = BuildRetCommand("sp_InsertSecondOrder_Seller", pa3, CommandType.StoredProcedure);
                            cmd3.Transaction = tran;
                            affectedRow = cmd3.ExecuteNonQuery();// 影响的行数
                        }
                    }
                    catch
                    {
                        tran.Rollback();
                        DeleteOrder(insertdOrderId);          // 有一个失败，就把订单删除
                    }
                    if (affectedRow > 0)
                    {
                        tran.Commit();
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                Connection.Close();
            }
            return true;
        }
        #endregion

        #region 二手操作区
        public DataSet SelectSecondOrderByUserId(int UserId)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from vi_Order where Ord_Idint = ");
            sqlStr.Append("(select Ord_Idint from tbSecondOrder_Seller ");
            sqlStr.Append("where Seller_Idint = @Seller_Idint)");

            SqlParameter[] pa = {
                new SqlParameter("@Seller_Idint",UserId)
                                };

            return ExecuteString(sqlStr.ToString(), pa, "tbOrder_Detail");
        }
        public int GetSellerId(int BookId)
        {
            StringBuilder sqlStr = new StringBuilder();
            DataSet ds = new DataSet();
            sqlStr.Append("select Seller_Idint from tbSecondBook_Seller ");
            sqlStr.Append("where Book_Idint = @Book_Idint");

            SqlParameter[] pa = {
                new SqlParameter("@Book_Idint",BookId)
                                };

            ds = ExecuteString(sqlStr.ToString(), pa, "tbSecondBook_Seller");
            if ((ds == null) ||
             (ds.Tables.Count == 0) ||
             (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0))
            {
                return -1;
            }
            else
            {
                return Int32.Parse(ds.Tables[0].Rows[0]["Seller_Idint"].ToString());
            } 
        }
        #endregion   
    }
}
